using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Users:DataAccess
    {
        public Users(string db_str)
        {
            CreateConnection(db_str);
        }
        public string GetRolesByUser(string user_name, string password)
        {
            string roles = string.Empty;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT roles FROM Users WHERE username=@username " +
                 "AND password=@password";

                // Fill our parameters

                cmd.Parameters.Add("@username", SqlDbType.NVarChar, 64).Value = user_name;
                System.Security.Cryptography.MD5CryptoServiceProvider md5Hasher = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hashedBytes;
                System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
                hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));

                cmd.Parameters.Add("@password", SqlDbType.NVarChar, 128).Value = 
                    System.BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
                conn.Open();
                roles = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                //throw ex;
                roles = string.Empty;
            }
            finally
            {
                conn.Close();
            }
            return roles;
        }
        public string GetClientByUser(string user_name)
        {
            string client_id = string.Empty;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT client_id FROM ClientContract WHERE user_name=@username";

                // Fill our parameters
                cmd.Parameters.Add("@username", SqlDbType.NVarChar, 64).Value = user_name;                
                conn.Open();
                client_id = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                //throw ex;
                client_id = string.Empty;
            }
            finally
            {
                conn.Close();
            }
            return client_id;
        }
        public DataSet GetAllMenus(string menu_type)
        {
            DataSet menus = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Menus where menu_type = @menu_type";
                cmd.Parameters.Add("@menu_type", SqlDbType.VarChar, 1).Value = menu_type; 
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(menus);
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
            return menus;
        }

        public DataSet GetMenusByUsername(string user_name, string menu_type)
        {
            DataSet menus = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_sel_MenusByUsername";
                cmd.Parameters.Add("@user_name", SqlDbType.NVarChar, 64).Value = user_name;
                cmd.Parameters.Add("@menu_type", SqlDbType.VarChar, 1).Value = menu_type;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(menus);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return menus;
        }
        public DataSet GetAllUsers(string user_name, string full_name, string email,
            string mobile, string roles, byte is_admin)
        {
            DataSet accounts = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Users WHERE (UserName = @user_name or @user_name = '')";
                cmd.Parameters.Add("@user_name", SqlDbType.NVarChar, 64).Value = user_name;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(accounts);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return accounts;
        }
        public string CreateUsers(string user_name, string password, int is_admin, string fullname, string email,
            string mobile, string branch_id, int roles, string created_by)
        {
            string err_code = string.Empty;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_ins_CreateUsers";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@user_name", SqlDbType.NVarChar, 64).Value = user_name;
                System.Security.Cryptography.MD5CryptoServiceProvider md5Hasher = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hashedBytes;
                System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
                hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));

                cmd.Parameters.Add("@password", SqlDbType.VarChar, 128).Value =
                    System.BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
                cmd.Parameters.Add("@IsAdmin", SqlDbType.TinyInt).Value = is_admin;
                cmd.Parameters.Add("@full_name", SqlDbType.NVarChar, 128).Value = fullname;
                cmd.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = email;
                cmd.Parameters.Add("@mobile", SqlDbType.NVarChar, 128).Value = mobile;
                cmd.Parameters.Add("@branch_id", SqlDbType.NVarChar, 128).Value = branch_id;
                cmd.Parameters.Add("@roles", SqlDbType.Int).Value = roles;
                cmd.Parameters.Add("@created_by", SqlDbType.NVarChar, 128).Value = created_by;
                
                conn.Open();
                err_code = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return err_code;
        }
        public void CreateUsersX(string user_name, string password, int is_admin, string fullname, string email,
            string mobile, string branch_id, int roles, string created_by, DateTime date_created)
        {            
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_Users";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = user_name;
                System.Security.Cryptography.MD5CryptoServiceProvider md5Hasher = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hashedBytes;
                System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
                hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));

                if (password != string.Empty)
                    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 128).Value =
                        System.BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
                else
                    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 128).Value = string.Empty;
                cmd.Parameters.Add("@IsAdministrator", SqlDbType.TinyInt).Value = is_admin;
                cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 128).Value = fullname;
                cmd.Parameters.Add("@Email", SqlDbType.VarChar, 60).Value = email;
                cmd.Parameters.Add("@Mobile", SqlDbType.VarChar, 20).Value = mobile;
                cmd.Parameters.Add("@BranchId", SqlDbType.VarChar, 6).Value = branch_id;
                cmd.Parameters.Add("@Roles", SqlDbType.Int).Value = roles;
                cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = date_created;
                cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 50).Value = created_by;
                conn.Open();
                cmd.ExecuteNonQuery().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public string ChangePassword(string user_name, string password, string new_pwd)
        {
            string err_code = string.Empty;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_upd_ChangePassword";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@user_name", SqlDbType.NVarChar, 64).Value = user_name;
                System.Security.Cryptography.MD5CryptoServiceProvider md5Hasher = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hashedBytes;
                System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
                hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));
                cmd.Parameters.Add("@password", SqlDbType.VarChar, 128).Value =
                    System.BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();

                hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(new_pwd));
                cmd.Parameters.Add("@new_pwd", SqlDbType.VarChar, 128).Value =
                    System.BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
                
                conn.Open();
                err_code = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return err_code;
        }

        public void UpdateRight(int user_id, int menu_id, string status)
        {            
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_upd_UserRight";
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                // Fill our parameters
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@user_id", SqlDbType.Int).Value = user_id;
                cmd.Parameters.Add("@menu_id", SqlDbType.Int).Value = menu_id;
                cmd.Parameters.Add("@status", SqlDbType.VarChar, 1).Value = status;
                cmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            
        }
    }
}
